// .........................................................................
// Title: sale_quantities.do
//
// Analyzes dynamics of corporate bond sales quantities by insurers and
// investment funds in the Great Recession
// .........................................................................

* ---------------------------------------
* Aggregate patterns: funds
* ---------------------------------------

* Obtain total and passive (household-driven) flows at individual fund level
use "$output/hd_panels/panel_fundlevel_cp_global_Q.dta", clear
keep if mns_class == "B" & ~inlist(mns_subclass, "S", "A", "LS", "SF", "SV")
keep if date_q >= tq(2008q1) & date_q <= tq(2009q1)
replace DomicileCountry = "EMU" if inlist(DomicileCountry, $eu1) | inlist(DomicileCountry, $eu2) | inlist(DomicileCountry, $eu3)
keep if inlist(DomicileCountry, "USA", "EMU", "CAN", "GBR") | inlist(DomicileCountry, "CHE", "AUS", "SWE", "DEN", "NOR")
collapse (sum) flow_usd flow_usd_bet mvusd, by(MasterPortfolioId DomicileCountryId date_q)

* Flows relative to 2008q1 level
gsort MasterPortfolioId
by MasterPortfolioId: gen _mvusd_2008q1 = mvusd if date_q == tq(2008q1)
by MasterPortfolioId: egen mvusd_2008q1 = max(_mvusd_2008q1)
drop _*
keep if date_q >= tq(2008q1) & date_q <= tq(2009q1)
gen flow_usd_pct_tot = flow_usd / mvusd_2008q1
gen flow_usd_pct_psv = flow_usd_bet / mvusd_2008q1
gen sd_flow_usd_pct_tot = flow_usd_pct_tot
gen sd_flow_usd_pct_psv = flow_usd_pct_psv
drop if mvusd_2008q1 <= 0 | missing(mvusd_2008q1)

* Collapse across funds
bys date_q: gen N = _N
collapse (mean) flow_usd_pct_tot flow_usd_pct_psv (sd) sd_flow_usd_pct_tot sd_flow_usd_pct_psv (firstnm) N [aw = mvusd_2008q1], by(date_q)
gen se_tot = sd_flow_usd_pct_tot / sqrt(N)
gen se_psv = sd_flow_usd_pct_psv / sqrt(N)
drop sd_* N

* Baseline year and confidence intervals
foreach var of varlist flow* se* {
    replace `var' = 0 if date_q == tq(2008q1)
}
gen cum_flow_tot = sum(flow_usd_pct_tot)
gen cum_flow_psv = sum(flow_usd_pct_psv)
cap drop *max *min
gen cum_flow_tot_max = cum_flow_tot + 1.96 * se_tot
gen cum_flow_psv_max = cum_flow_psv + 1.96 * se_psv
gen cum_flow_tot_min = cum_flow_tot - 1.96 * se_tot
gen cum_flow_psv_min = cum_flow_psv - 1.96 * se_psv
save "$tmp/mutual_fund_flows_gr", replace

* ---------------------------------------
* Aggregate patterns: insurers
* ---------------------------------------

* Select corporate bond sample for insurers
use "$insurance_holdings/all_insurance_master", clear
keep if date_q >= tq(2008q1) & date_q <= tq(2009q4)
drop if missing(cusip)
mmerge cusip using "$raw/cmns/gcap_security_master_cusip.dta", unmatched(m) ukeep(class_code2)
keep if class_code2 == "BC"
gcollapse (sum) carrying_value conditional_fair_value actual_cost shares_owned aggregate_par_value marketvalue_usd, by(holder_name cusip date_q)
save "$tmp/bonds_insurers_summary", replace

* Flows relative to 2008q1
use "$tmp/bonds_insurers_summary", clear
gsort holder_name cusip
gen _shares_08q1 = shares_owned if date_q == tq(2008q1)
gen _value_08q1 = marketvalue_usd if date_q == tq(2008q1)
by holder_name cusip: egen shares_08q1 = max(_shares_08q1)
by holder_name cusip: egen value_08q1 = max(_value_08q1)
drop _*
gen pct_flow = shares_owned / shares_08q1 - 1
drop if value_08q1 <= 0 | missing(value_08q1)

* Winsorize giant outliers, likely misreporting
winsor2 pct_flow, cuts(1 99) replace

* Collapse across insurers
bys holder_name: gen N = 1 if _n == 1
egen totN = sum(N)
gen sd_flow = pct_flow
collapse (mean) pct_flow (sd) sd_flow (firstnm) totN [aw = value_08q1], by(date_q)
gen se = sd_flow / sqrt(totN)
foreach var of varlist *flow* sd* se* {
    replace `var' = 0 if date_q == tq(2008q1)
}
gen cum_flow = sum(pct_flow)
rename cum_flow cum_flow_insurer_tot
gen cum_flow_insurer_tot_min = cum_flow_insurer_tot - 1.96 * se
gen cum_flow_insurer_tot_max = cum_flow_insurer_tot + 1.96 * se
keep date_q cum*
save "$tmp/insurer_dynamics_cumulative", replace

* ---------------------------------------
* Plot funds and insurers together
* ---------------------------------------

* Construct and output plot (Figure 4)
use "$tmp/mutual_fund_flows_gr", clear
mmerge date_q using "$tmp/insurer_dynamics_cumulative", unmatched(m)
drop _merge

line cum_flow_tot cum_flow_psv cum_flow_insurer_tot date_q, graphregion(color(white) ) xtitle("") ///
        lcolor(red%50 green%50 blue%50) lpattern(solid dash solid) ///
        legend(label(1 "Funds: Total") label(2 "Funds: Household-Driven") label(3 "Insurers") order(1 2 3) cols(3)) ///
    || rcap cum_flow_tot_min cum_flow_tot_max date_q, color(red) ///
    || rcap cum_flow_psv_min cum_flow_psv_max date_q, color(green) ///
    || rcap cum_flow_insurer_tot_max cum_flow_insurer_tot_min date_q, color(blue) ///
        title("", size(medsmall) color(black)) ///
        ytitle("Cumulative Corporate Bond Sales") xsize(7.5) ylab(-.15 "-15%" -.1 "-10%" -.05 "-5%" 0 "0%" .05 "5%", angle(0)) ///
    || rarea cum_flow_tot_min cum_flow_tot_max date_q, color(red%10) ///
    || rarea cum_flow_psv_min cum_flow_psv_max date_q, color(green%10) ///
    || rarea cum_flow_insurer_tot_max cum_flow_insurer_tot_min date_q, color(blue%10)

graph export "$graphs/portfolio_sales.pdf", as(pdf) replace
